package com.senac.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.senac.model.Aluno;
import com.senac.parser.AlunoParser;
import com.senac.parser.Parser;

public class AlunoDao {
	
	public List<Aluno> getAlunos() {		
		Connection con =  null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Aluno> list = new ArrayList<>();
		try {
			con = ConnectionFactory.getConnection();
			ps = con.prepareStatement("SELECT A.ID, " +
									  "		  A.NOME, " +
									  "		  A.RG, " +
									  "		(SELECT SUM(D.CARGA_HORARIA) " +
									  "		 FROM DISCIPLINA D  " +
									  "		 INNER JOIN ALUNO_DISCIPLINA AD ON AD.ID_DISCIPLINA = D.ID " +
									  "		WHERE A.ID = AD.ID_ALUNO) AS CARGA_HORARIA " +
									  "FROM ALUNO A");

			rs = ps.executeQuery();
			Parser<Aluno> aParser = new AlunoParser(rs);
			while(rs.next()){
				list.add(aParser.parse());
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			BDUtil.finalizeConnection(rs, ps, con);
		}				
		return list;
	}

	
	public Aluno getAlunoById(Integer id) {
		Connection con =  null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Aluno aluno = null;
		try {
			con = ConnectionFactory.getConnection();
			ps = con.prepareStatement("SELECT A.ID, " +
									  "		 A.NOME, " +
									  "		 A.RG, " +
									  "		(SELECT SUM(D.CARGA_HORARIA) " +
									  "		 FROM DISCIPLINA D  " +
									  "		 INNER JOIN ALUNO_DISCIPLINA AD ON AD.ID_DISCIPLINA = D.ID " +
									  "		 WHERE A.ID = AD.ID_ALUNO) AS CARGA_HORARIA " +
									  "FROM ALUNO A " +
									  "WHERE A.ID = ?");
			ps.setInt(1, id);
			rs = ps.executeQuery();
			Parser<Aluno> aParser = new AlunoParser(rs);
			if(rs.next()){
				aluno = aParser.parse();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			BDUtil.finalizeConnection(rs, ps, con);
		}				
		return aluno;
	}

	
	
	public boolean save(Aluno aluno) {
		Connection con =  null;
		PreparedStatement ps = null;
		try {
			con = ConnectionFactory.getConnection();
			ps = con.prepareStatement("INSERT INTO ALUNO (nome, carga_horaria, rg) VALUES (?, ?, ?)");			
			ps.setString(1, aluno.getNome());
			ps.setInt(2, aluno.getCargaHoraria());
			ps.setString(3, aluno.getRg());
			return (ps.executeUpdate() > 0);
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			BDUtil.finalizeConnection(ps, con);
		}
	}

	
	
	
	public boolean update(Aluno aluno) {
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = ConnectionFactory.getConnection();
			ps = con.prepareStatement("UPDATE ALUNO SET nome = ?, carga_horaria = ?, rg = ? WHERE ID = ?");
			ps.setString(1, aluno.getNome());
			ps.setInt(2, aluno.getCargaHoraria());
			ps.setString(3, aluno.getRg());
			ps.setInt(4, aluno.getId());
			return ps.executeUpdate() > 0;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			BDUtil.finalizeConnection(ps, con);
		}

	}

	
	public boolean delete(Integer id) {
		Connection con =  null;
		PreparedStatement ps = null;
		try {
			con = ConnectionFactory.getConnection();
			ps = con.prepareStatement("DELETE FROM ALUNO WHERE ID = ?");			
			ps.setInt(1, id);
			return ps.executeUpdate() > 0;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			BDUtil.finalizeConnection(ps, con);
		}
	}
	
	
	public List<Aluno> getAlunosByDisciplina(Integer idDisciplina) {
		Connection con =  null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Aluno> list = new ArrayList<>();
		try {
			con = ConnectionFactory.getConnection();
			ps = con.prepareStatement("SELECT DISTINCT A.ID, " +
									  "				   A.NOME " +
									  "FROM ALUNO A " +
									  "INNER JOIN ALUNO_DISCIPLINA AD ON AD.ID_ALUNO = A.ID " +
									  "WHERE AD.ID_DISCIPLINA = ?");
			ps.setInt(1, idDisciplina);
			rs = ps.executeQuery();
			Parser<Aluno> aParser = new AlunoParser(rs);
			while(rs.next()){
				list.add(aParser.parse());
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			BDUtil.finalizeConnection(rs, ps, con);
		}				
		return list;
	}	
	
	
	public boolean matricular(int idAluno, int idDiscipplina) {
		Connection con =  null;
		PreparedStatement ps = null;
		try {
			con = ConnectionFactory.getConnection();
			ps = con.prepareStatement("INSERT INTO ALUNO_DISCIPLINA (ID_ALUNO, ID_DISCIPLINA) VALUES (?, ?)");			
			ps.setInt(1, idAluno);
			ps.setInt(2, idDiscipplina);
			return (ps.executeUpdate() > 0);
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			BDUtil.finalizeConnection(ps, con);
		}
	}
	
	

}
